package com.propertypoint.persitence.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.propertypoint.exception.DatabaseException;
import com.propertypoint.presentation.model.Advertiesment;
import com.propertypoint.util.DBUtil;

public class AdvertiesmentDAOImpl implements AdvertiesmentDAO {

	private static final String INSERT_ADD = "INSERT INTO agent_advertiesment"
			+ "(end_date,agent_id,status) VALUES"
			+ "(?,?,?)";

	private static final String GET_PUBLISHED_ADDS="SELECT a.id,a.published_date,a.end_date,a.status,b.profile_picture,b.phone_number,b.email,b.first_name,b.last_name,b.company_name FROM  agent_advertiesment a left join agent b on a.agent_id=b.id where status='APPROVED' and end_date> CURRENT_TIMESTAMP"; 
	
	public void inserAdd(Advertiesment add) throws Exception {
		Connection con = DBUtil.getDBUtil().getConnection();
		PreparedStatement preparedStatement = con.prepareStatement(INSERT_ADD,
				Statement.RETURN_GENERATED_KEYS);

		preparedStatement.setTimestamp(1, new Timestamp(add.getEndDate().getTime()));
		preparedStatement.setLong (2, add.getAgentId());// agent id		 
		preparedStatement.setString(3, "PENDING");// status
		 
		try {
			// execute insert SQL stetement
			preparedStatement.executeUpdate();
		} catch (Exception e) {
			preparedStatement.close();
			con.close();
			throw new DatabaseException(e.toString());
		}
		finally {
			preparedStatement.close();
			con.close();
		}

	}
	
	@Override
	public List<Advertiesment> getPublishedAdds(){
		List<Advertiesment> L = new ArrayList<Advertiesment>();
		Connection con = null;
		try {
			con = DBUtil.getDBUtil().getConnection();
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(GET_PUBLISHED_ADDS);
			while (rs.next()){
				Advertiesment ad = new Advertiesment();
				ad.setId(rs.getString("id"));
				ad.setPublishedDate(rs.getDate("published_date"));
				ad.setEndDate(rs.getDate("end_date"));
				ad.setStatus(rs.getString("status"));
				ad.setEmail(rs.getString("email"));
				ad.setMobileNo(" +65 "+rs.getString("phone_number"));
				if (rs.getString("first_name") != null
						&& rs.getString("last_name") != null
						&& rs.getString("company_name") != null) {
					String message = rs.getString("first_name").toUpperCase() + " "
							+ rs.getString("last_name").toUpperCase();
					ad.setMessage(message);
					
					
				}
				String company = rs.getString("company_name");
				
				if(company != null)
					ad.setCompany(company);
				
				
				ad.setPicture("img/profilepics/"+rs.getString("profile_picture"));
				L.add(ad);
			}
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		 
		
		
		return L;
	}
}
